<?php
/**
 * Pipahr
 *
 * @author Wei Ma
 * @copyright 2013 pipahr.com
 * 
 * @since   3.0
 */

JTable::addIncludePath(JPATH_ADMINISTRATOR.'/components/com_puser/tables');
JTable::addIncludePath(JPATH_ADMINISTRATOR.'/components/com_jobs/tables');

/**
 * Resume tag system
 */
class PResumesModelTag extends JModelLegacy
{
   /**
    * Save resume tags
    *
    * @param   array  $data  Resume tags data array
    * @return  boolean  
    *
    * @since   3.0
    */
   function saveResumeTags($data) {
      $tagid_resumeids = array();
      
      if (empty($data) || !isset($data['jobid']) || !isset($data['resumeid']) || !isset($data['owner'])) {
         $this->setError('系统错误');
         return false;
      }
      
      if (PFactory::isJobseekerUser() || !PCompanyHelper::checkJobManagerPermissions($data['jobid'])) {
         $this->setError('没有权限');
         return false;
      }
      
      if (!empty($data['tags']) && count($data['tags'])) {
      
         $data['resumeid'] = intval($data['resumeid']);
         $data['owner'] = intval($data['owner']);
         
         $db = PFactory::getDbo();
         
         $tagsArray=explode(',', $data['tags']);
         /* find out how many tags in the list */
         $query='SELECT hjttd.tagid, hjtt.name FROM *PREFIX*hr_jobseeker_tag_term_details AS hjttd 
                 LEFT JOIN *PREFIX*hr_jobseeker_tag_term AS hjtt ON hjtt.id=hjttd.tagid
                 WHERE hjttd.jobseeker_id=' . $data['resumeid'] . ' AND hjttd.owner=' . $data['owner'];
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $currentTagsArray = $db->loadAssocList('tagid', 'name');
         $currentTagsArray = $currentTagsArray ? $currentTagsArray: array();
   
         // find out new items not in currentTagsArray which is current list
         $addTagsArray = array_diff($tagsArray, array_values($currentTagsArray));
   
         // find out removed items , so we can remove it later
         $removeTagsArray = array_diff($currentTagsArray, $tagsArray);
          
   
         // we don't need to add if $addTagsArray is empty 
         if(count($addTagsArray) > 0) {
            $safeAddTagsNames = array();
            foreach($addTagsArray as $addTagName) {
               $addTagName = $db->quote($addTagName);
               $safeAddTagsNames[] = $addTagName;
            }
   
            /* some probably already in list, so query first */
            $query = 'SELECT id, name, verified FROM *PREFIX*hr_jobseeker_tag_term
                      WHERE (owner=0 OR owner=' . $data['owner'] . ') AND name IN (' . implode(',', $safeAddTagsNames) . ')';
            $db->setQuery(PUtil::replaceOCPrefix($query));
            $existTagsArray = $db->loadAssocList('name');
            $existTagsArray = $existTagsArray ? $existTagsArray: array();
   
            /* This is new tags array */
            $newTagsArray = array_diff($addTagsArray, array_keys($existTagsArray));
   
            /* do batch add and obtain its id */
            $newTagIds = $this->insertTags($newTagsArray, $data['owner']);
   
            if(!empty($newTagIds)) {
               foreach($newTagIds as $newTagId) {
                  if(isset($newTagId['id'])) {
                     $tagid_resumeids[] = array($newTagId['id'], $data['resumeid']);
                  }
               }
            }
   
            if(!empty($existTagsArray)) {
               foreach($existTagsArray as $name=>$newTagId) {
                  if(isset($newTagId['id'])) {
                     $tagid_resumeids[] = array($newTagId['id'], $data['resumeid']);
                  }
               }
            }
         }
      
         /* remove the items which have been removed from list */
         if(!empty($removeTagsArray)) {
            $deltids = "(" . implode(",", array_keys($removeTagsArray)) . ")";
            $deleteTags = 'DELETE FROM *PREFIX*hr_jobseeker_tag_term_details 
                           WHERE jobseeker_id=' . $data['resumeid'] . ' AND owner=' . intval($data['owner']) . ' AND tagid IN ' . $deltids;
            $db->setQuery(PUtil::replaceOCPrefix($deleteTags));
            $db->execute();
         }
         
         $this->insertTagsDetails($tagid_resumeids, $data['owner']);

         // Event Driven Cache/Object Caching - clear any cache related to affected tables 
         CacheHelper::triggerRemove($data['owner'], array('*PREFIX*hr_jobseeker_token-'.$data['resumeid']));
      }
      
      return true;
   }
   
   function addResumeTags($data) {
      $tagid_resumeids = array();
      
      if (empty($data) || !isset($data['jobid']) || !isset($data['resumeid']) || !isset($data['owner'])) {
         $this->setError('系统错误');
         return false;
      }
      
      if (PFactory::isJobseekerUser() || !PCompanyHelper::checkJobManagerPermissions($data['jobid'])) {
         $this->setError('没有权限');
         return false;
      }
      
      if (!empty($data['tags']) && count($data['tags'])) {
      
         $data['resumeid'] = intval($data['resumeid']);
         $data['owner'] = intval($data['owner']);
          
         $db = PFactory::getDbo();
          
         $tagsArray=explode(',', $data['tags']);
         /* find out how many tags in the list */
         $query='SELECT hjttd.tagid, hjtt.name FROM *PREFIX*hr_jobseeker_tag_term_details AS hjttd
                 LEFT JOIN *PREFIX*hr_jobseeker_tag_term AS hjtt ON hjtt.id=hjttd.tagid
                 WHERE hjttd.jobseeker_id=' . $data['resumeid'] . ' AND hjttd.owner=' . $data['owner'];
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $currentTagsArray = $db->loadAssocList('tagid', 'name');
         $currentTagsArray = $currentTagsArray ? $currentTagsArray: array();
          
         // find out new items not in currentTagsArray which is current list
         $addTagsArray = array_diff($tagsArray, array_values($currentTagsArray));
         
         $currentTagsCount = count($currentTagsArray);
         $addTagsCount = count($addTagsArray);
         $totalTagsCount = $currentTagsCount + $addTagsCount;
         if ($totalTagsCount > 5) {
            $this->setError('每份简历最多添加5个标签');
            return false;
         }
          
         // we don't need to add if $addTagsArray is empty
         if(count($addTagsArray) > 0) {
            $safeAddTagsNames = array();
            foreach($addTagsArray as $addTagName) {
               $addTagName = $db->quote($addTagName);
               $safeAddTagsNames[] = $addTagName;
            }
             
            /* some probably already in list, so query first */
            $query = 'SELECT id, name, verified FROM *PREFIX*hr_jobseeker_tag_term
                      WHERE (owner=0 OR owner=' . $data['owner'] . ') AND name IN (' . implode(',', $safeAddTagsNames) . ')';
            $db->setQuery(PUtil::replaceOCPrefix($query));
            $existTagsArray = $db->loadAssocList('name');
            $existTagsArray = $existTagsArray ? $existTagsArray: array();
             
            /* This is new tags array */
            $newTagsArray = array_diff($addTagsArray, array_keys($existTagsArray));
             
            /* do batch add and obtain its id */
            $newTagIds = $this->insertTags($newTagsArray, $data['owner']);
             
            if(!empty($newTagIds)) {
               foreach($newTagIds as $newTagId) {
                  if(isset($newTagId['id'])) {
                     $tagid_resumeids[] = array($newTagId['id'], $data['resumeid']);
                  }
               }
            }
             
            if(!empty($existTagsArray)) {
               foreach($existTagsArray as $name=>$newTagId) {
                  if(isset($newTagId['id'])) {
                     $tagid_resumeids[] = array($newTagId['id'], $data['resumeid']);
                  }
               }
            }
         }
       
         $this->insertTagsDetails($tagid_resumeids, $data['owner']);
         $this->updateResumeTagsHits($addTagsArray);
         PResumesHelper::updateProcessed($data['resumeid']);
      }
      
      return true;
   }
   
   function insertTags($names, $owner) {
      if(empty($names) || !$owner)
         return null;
      
      $db = PFactory::getDbo();
      
      // For any tags , the verified is default to 1
      $verified = 1;
   
      $safeNames = array();
      $queries = array();
      $query="INSERT IGNORE INTO *PREFIX*hr_jobseeker_tag_term (name, owner, verified, created) VALUES ";
      foreach($names as $name) {
         $name = TagsHelper::isValidName($name);
         if($name === false)
            continue;
         $name = $db->quote($name);
         $safeNames[] = $name;
   
         $queries[] = "(" . $name . ", " . intval($owner) . ", " . $verified . ", UTC_TIMESTAMP())";
      }
      $query .= implode(",", $queries);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();
   
      /* get new added ids */
      $query = "SELECT id FROM *PREFIX*hr_jobseeker_tag_term WHERE owner=" . intval($owner) . " AND name IN (" . implode(',', $safeNames). ")";
      $db->setQuery(PUtil::replaceOCPrefix($db->useLastConnection($query)));
      return $db->loadAssocList();
   }
   
   function insertTagsDetails($tagid_resumeids=array(), $owner) {
      if(empty($tagid_resumeids) || !$owner)
         return ;
      
      $db = PFactory::getDbo();
   
      $values = array();
      foreach($tagid_resumeids as $tagid_resumeid) {
         list($tagid, $resumeid) = $tagid_resumeid;
         // Event Driven Cache/Object Caching - clear any cache related to affected tables 
         CacheHelper::triggerRemove(intval($owner), array('*PREFIX*hr_jobseeker_token-'.intval($resumeid)));

         $values[] = '('.intval($tagid).', '.intval($owner).', '.intval($resumeid).', UTC_TIMESTAMP())';
      }
   
      $insertQuery='INSERT IGNORE INTO *PREFIX*hr_jobseeker_tag_term_details (tagid, owner, jobseeker_id, added) VALUES ' . implode(",", $values);
      $db->setQuery(PUtil::replaceOCPrefix($insertQuery));
      $db->execute();

   }
   
   function updateResumeTagsHits($tagnames) {
      if(empty($tagnames))
         return ;
      
      $db = PFactory::getDbo();
      $user = PFactory::getMasterUser();
      
      // stay on master to read-your-write      
      $db->stayOnMaster(); 

      foreach($tagnames as $tagname) {
         $tagname = $db->quote($tagname);
         $safeTagNames[] = $tagname;
      }
      $query = 'SELECT id, owner FROM *PREFIX*hr_jobseeker_tag_term WHERE verified=1 AND (owner=0 OR owner=' . $user->id . ')
                AND name IN (' . implode(',', $safeTagNames) . ')';
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $resumeTags = $db->loadObjectList();
      
      $systemTags = array();
      $companyTags = array();
      foreach ($resumeTags as $resumeTag) {
         if ($resumeTag->owner == 0) {
            $systemTags[] = $resumeTag->id;
         } else {
            $companyTags[] = $resumeTag->id;
         }
      }
      
      if (!empty($systemTags)) {
         $query = 'UPDATE *PREFIX*hr_jobseeker_tag_term AS t SET t.hits=(SELECT hits FROM 
   	             (SELECT COUNT(*) AS hits, b.id FROM *PREFIX*hr_jobseeker_tag_term_details AS a 
   			       LEFT JOIN *PREFIX*hr_jobseeker_tag_term AS b ON b.id=a.tagid 
                   WHERE b.id IN (' . implode(',', $systemTags) . ') GROUP BY b.id ) AS h WHERE h.id=t.id) 
                   WHERE t.id IN (' . implode(',', $systemTags) . ')';
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();
      }
      
      if (!empty($companyTags)) {
         $query = 'UPDATE *PREFIX*hr_jobseeker_tag_term AS t SET t.hits=(SELECT hits FROM
   	             (SELECT COUNT(*) AS hits, b.id FROM *PREFIX*hr_jobseeker_tag_term_details AS a
   			       LEFT JOIN *PREFIX*hr_jobseeker_tag_term AS b ON b.id=a.tagid
                   WHERE a.owner=' . $user->id . ' AND b.id IN (' . implode(',', $companyTags) . ') GROUP BY b.id ) AS h WHERE h.id=t.id)
                   WHERE t.id IN (' . implode(',', $companyTags) . ')';
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();
      }

      // leave master enforcement now 
      $db->leaveOnMaster(); 
      
      return true;
   }

   function getResumeTags($resumeid, $companyid = 0) {
      if(empty($resumeid) || empty($companyid))
         return null;
      
      $db = PFactory::getDbo();
      
      $query = 'SELECT hjtt.id, hjtt.name FROM *PREFIX*hr_jobseeker_tag_term_details AS hjttd
                LEFT JOIN *PREFIX*hr_jobseeker_tag_term AS hjtt ON hjtt.id=hjttd.tagid
                WHERE hjtt.verified=1 AND hjttd.jobseeker_id=' . intval($resumeid) . ' AND hjttd.owner=' . $companyid;
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObjectList();
   }
   
   function getResumeHotTags($taglimit = 10) {
      $db = PFactory::getDbo();
      $user = PFactory::getMasterUser();
   
      $query = 'SELECT name FROM *PREFIX*hr_jobseeker_tag_term WHERE verified=1 AND (owner=0 OR owner=' . $user->id . ') ORDER BY owner DESC, hits DESC ';
      if ($taglimit) {
         $query .= ' LIMIT ' . $taglimit;
      }
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObjectList();
   }

   function getSearchTags($search = null, $taglimit = 20) {
      if(empty($search))
         return null;
      
      $db = PFactory::getDbo();
      $user = PFactory::getMasterUser();
      
      $query = 'SELECT name FROM *PREFIX*hr_jobseeker_tag_term WHERE verified=1 AND (owner=0 OR owner=' . $user->id . ') AND name LIKE ' . $db->quote('%' . $search . '%') . 
               'ORDER BY CASE WHEN name LIKE ' . $db->quote($search . ' %') . ' THEN 0
                              WHEN name LIKE ' . $db->quote($search .'%') . ' THEN 1
                              WHEN name LIKE ' . $db->quote('% ' . $search . '%') . ' THEN 2
                              ELSE 3
                         END';
      if ($taglimit) {
         $query .= ' LIMIT ' . $taglimit;
      }
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObjectList();
   }
   
   function removeResumeTag($jobid, $resumeid, $tagid) {
      if(empty($jobid) || empty($resumeid) || empty($tagid)) {
         $this->setError('系统错误');
         return false;
      }
      
      if (PFactory::isJobseekerUser() || !PCompanyHelper::checkJobManagerPermissions($jobid)) {
         $this->setError('没有权限');
         return false;
      }
      
      $db = PFactory::getDbo();
      $user = PFactory::getMasterUser();
      $query = 'DELETE FROM *PREFIX*hr_jobseeker_tag_term_details
                WHERE jobseeker_id=' . $resumeid . ' AND owner=' . $user->id . ' AND tagid=' . $tagid;
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();

      // Event Driven Cache/Object Caching - clear any cache related to affected tables 
      CacheHelper::triggerRemove($user->id, array('*PREFIX*hr_jobseeker_token-'.intval($resumeid)));

 
      $query = 'SELECT name FROM *PREFIX*hr_jobseeker_tag_term WHERE id=' . $tagid;
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $tagname = $db->loadResult();
      $this->updateResumeTagsHits(array($tagname));

      return true;
   }

}
